package main

import (
	"fmt"
	"whoscored/util/mysql"
	"strings"
	"strconv"
	"time"
	"github.com/xuri/excelize"
	"whoscored/qiutan"
	"math"
)

func lastSeasonRating(team interface{},t int) []interface{} {
	sql:="select * from team_season where type=? and teamId=? and season=? and regionCode=? and tournamentName=?"
	data:=team.(map[string]interface{})
	season:=data["season"].(string)
	seasonTarget:=""
	if data["regionName"]=="Argentina"{

		if season=="2016/2017"{
			seasonTarget="2016"
		}

	}else{
		if strings.Contains(season,"/"){
			slice:=strings.Split(season,"/")
			a,_:=strconv.ParseFloat(slice[0],64)
			b,_:=strconv.ParseFloat(slice[1],64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)+"/"+strconv.FormatFloat(b-1,'f',-1,64)

		}else{
			a,_:=strconv.ParseFloat(season,64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)
		}
	}


	result:=mysql.FindFirst(sql,t,data["teamId"],seasonTarget,data["regionCode"],data["tournamentName"])

	if result["rating"]==nil{
		//取联赛本赛季的平均分
		sql2:="select avg(rating) as rating from team_season where type=? and season=? and regionCode=? and tournamentName=?"
		result2:=mysql.FindFirst(sql2,t,seasonTarget,data["regionCode"],data["tournamentName"])
		if result2["rating"]==nil{
			return []interface{}{float64(5)}
		}
		return []interface{}{
			result2["rating"],
		}
	}

	return []interface{}{
		result["rating"],
	}
}

//本赛季已完成10场比赛
func isTen(team interface{},t int) bool {
	sql:="select count(*) as count from team_match where teamId=? and startTimeUtc<? " +
		"and season=? and regionCode=? and tournamentName=?"
	if t==1{
		sql+=" and isHome=1"
	}
	if t==2{
		sql+=" and isHome=0"
	}
	data:=team.(map[string]interface{})
	result:=mysql.FindFirst(sql,data["teamId"],data["startTimeUtc"],data["season"],data["regionCode"],data["tournamentName"])

	if result["count"].(int64)>=10{
		return true
	}
	return false
}
//本赛季评分
func seasonRating(team interface{},t int) []interface{} {
	sql:="select avg(rating) as rating from team_match where teamId=? and startTimeUtc<? " +
		"and season=? and regionCode=? and tournamentName=?"
	if t==1{
		sql+=" and isHome=1"
	}
	if t==2{
		sql+=" and isHome=0"
	}
	data:=team.(map[string]interface{})
	result:=mysql.FindFirst(sql,data["teamId"],data["startTimeUtc"],data["season"],data["regionCode"],data["tournamentName"])

	if result["rating"]==nil{
		return []interface{}{float64(5)}
	}
	return []interface{}{
		result["rating"],
	}
}
//球队评分
func teamRating(team interface{},t int) float64 {
	rating:=float64(5)
	if isTen(team,t){
		rating=seasonRating(team,t)[0].(float64)
	}else{
		rating=lastSeasonRating(team,t)[0].(float64)
	}
	return rating
}
//球队最近一场比赛
func teamLastMatch(team interface{},rating float64)[]interface{} {

	sql:="select * from `match` where (homeId=? or awayId=?) and startTimeUtc<? order by startTimeUtc desc"
	data:=team.(map[string]interface{})
	result:=mysql.FindFirst(sql,data["teamId"],data["teamId"],data["startTimeUtc"])

	teamId,_:=strconv.ParseInt(data["teamId"].(string),10,64)
	homeId,_:=result["homeId"].(int64)
	isHome:=0
	if teamId==homeId{
		isHome=1
	}
	homeScore:=result["homeScore"].(int64)
	awayScore:=result["awayScore"].(int64)
	score:=homeScore-awayScore

	betId:=result["awayId"].(int64)
	if isHome==0{
		score=awayScore-homeScore
		betId=homeId
	}
	//与对手的评分差

	d:=map[string]interface{}{
		"teamId":betId,
		"startTimeUtc":data["startTimeUtc"],
		"season":data["season"],
		"regionCode":data["regionCode"],
		"tournamentName":data["tournamentName"],
	}
	betRating:=teamRating(d,0)
	ratingDiff:=rating-betRating
	return []interface{}{score,ratingDiff,isHome}
}
func playerRating(playerId interface{},startTimeUtc interface{},isHome bool) (float64) {
	sql:="select avg(rating) as rating from " +
		"(select rating from player_match where playerId=? and startTimeUtc<? and rating>0 ";
	if isHome{
		sql+=" and isHome=1"
	}else{
		sql+=" and isHome=0"
	}
	sql+=" order by startTimeUtc desc limit 3)" +
		" as t"
	result:=mysql.FindFirst(sql,playerId,startTimeUtc)
	if result["rating"]==nil{
		return float64(5)
	}
	return result["rating"].(float64)
}
//球队球员评分
func playersRating(team interface{},isSub int,isHome bool) float64 {
	data:=team.(map[string]interface{})
	sql:="select * from player_match where matchId=? and teamId=? and rating>0"
	if isSub==0{
		sql+=" and isSub=0"
	}else if isSub==1{
		sql+=" and isSub=1"
	}
	result:=mysql.FindAll(sql,data["matchId"],data["teamId"])

	sum:=float64(0)
	i:=float64(0)
	for _,v:=range result{
		rating:=playerRating(v["playerId"],v["startTimeUtc"],isHome)
		sum=sum+rating
		i++
	}

	if i==0{
		return float64(5)
	}
	return sum/i
}
//球员是否是主力
func isMain(playerId interface{},startTimeUtc interface{}) bool {
	sql:="select avg(minutes_played) as minutes from (select * from player_match where playerId=? " +
		"and startTimeUtc<? order by startTimeUtc desc limit 10) as t"
	result:=mysql.FindFirst(sql,playerId,startTimeUtc)
	if result["minutes"].(float64)<50{
		return false
	}
	return true
}
/**
球员分析
GK：守门员
DL：左后卫
DC：中后卫
DR：右后卫
AML：左边锋
AMC：中边锋
AMR：右边锋
Sub：替补
ML：左前卫
MC：中前卫
MR：右前卫
FW：前锋
FWL：左前锋
FWR：右前锋
DML：左后腰
DMC：中场后腰。后腰是位置较靠后的中场，是球队中场与防线之间的重要纽带
DMR：右后腰
 */
func playerStat(team interface{},isHome bool) []float64 {
	data:=team.(map[string]interface{})
	sql:="select playerId,position,startTimeUtc from player_match where matchId=? and teamId=?"
	result:=mysql.FindAll(sql,data["matchId"],data["teamId"])
	ratingMap:=map[string]float64{
		"GK":float64(0),
		"D":float64(0),
		"AM":float64(0),
		"M":float64(0),
		"FW":float64(0),
		"DM":float64(0),
		"Sub":float64(0),
	}
	countMap:=map[string]float64{
		"GK":float64(1),
		"D":float64(0),
		"AM":float64(0),
		"M":float64(0),
		"FW":float64(0),
		"DM":float64(0),
		"Sub":float64(0),
	}
	maxRating:=float64(0)
	subMaxRating:=float64(0)
	for _,v:=range result{
		rating:=playerRating(v["playerId"],v["startTimeUtc"],isHome)
		pos:=v["position"].(string)

		if pos=="GK"{
			ratingMap["GK"]=rating
		}
		if pos=="DL"||pos=="DC"||pos=="DR"{
			ratingMap["D"]=ratingMap["D"]+rating
			countMap["D"]=countMap["D"]+1
		}
		if pos=="AML"||pos=="AMC"||pos=="AMR"{
			ratingMap["AM"]=ratingMap["AM"]+rating
			countMap["AM"]=countMap["AM"]+1
			if rating>maxRating{
				maxRating=rating
			}
		}
		if pos=="ML"||pos=="MC"||pos=="MR"{
			ratingMap["M"]=ratingMap["M"]+rating
			countMap["M"]=countMap["M"]+1
			if rating>maxRating{
				maxRating=rating
			}
		}
		if pos=="FW"||pos=="FWL"||pos=="FWR"{
			ratingMap["FW"]=ratingMap["FW"]+rating
			countMap["FW"]=countMap["FW"]+1
			if rating>maxRating{
				maxRating=rating
			}
		}
		if pos=="DML"||pos=="DMC"||pos=="DMR"{
			ratingMap["DM"]=ratingMap["DM"]+rating
			countMap["DM"]=countMap["DM"]+1
		}
		if pos=="Sub"{
			ratingMap["Sub"]=ratingMap["Sub"]+rating
			countMap["Sub"]=countMap["Sub"]+1

			if rating>subMaxRating{
				subMaxRating=rating
			}
		}
	}
	if countMap["D"]>0{
		ratingMap["D"]=ratingMap["D"]/countMap["D"]
	}
	if countMap["AM"]>0{
		ratingMap["AM"]=ratingMap["AM"]/countMap["AM"]
	}
	if countMap["M"]>0{
		ratingMap["M"]=ratingMap["M"]/countMap["M"]
	}
	if countMap["FW"]>0{
		ratingMap["FW"]=ratingMap["FW"]/countMap["FW"]
	}
	if countMap["DM"]>0{
		ratingMap["DM"]=ratingMap["DM"]/countMap["DM"]
	}
	if countMap["Sub"]>0{
		ratingMap["Sub"]=ratingMap["Sub"]/countMap["Sub"]
	}
	//fmt.Println(maxRating,subMaxRating,countMap,ratingMap)

	//rd:=(ratingMap["D"]+ratingMap["DM"])/(countMap["D"]+countMap["DM"])
	//ra:=(ratingMap["AM"]+ratingMap["M"]+ratingMap["FW"])/(countMap["AM"]+countMap["M"]+countMap["FW"])

	return []float64{
		maxRating,subMaxRating,
		countMap["GK"],countMap["D"],countMap["AM"],countMap["M"],countMap["FW"],countMap["DM"],countMap["Sub"],
		ratingMap["GK"],ratingMap["D"],ratingMap["AM"],ratingMap["M"],
		ratingMap["FW"],ratingMap["DM"],ratingMap["Sub"],

	}
}
//最近6场胜负及与对手差距
func last6Match(team interface{},rating float64) ([]interface{},[]interface{}) {

	home:=team.(map[string]interface{})

	/*sql:="select * from (" +
		"select * from `match` t1 where t1.homeId=? and t1.startTimeUtc<? and t1.regionCode=? and t1.tournamentName=? " +
		"union all " +
		"select * from `match` t2 where t2.awayId=? and t2.startTimeUtc<? and t2.regionCode=? and t2.tournamentName=?) " +
		"as t order by t.startTimeUtc desc limit 6"*/
	sql:="select tm.*,m.homeId,m.awayId from team_match tm " +
		"left join `match` m on m.matchId=tm.matchId " +
		"where tm.teamId=? and tm.startTimeUtc<? " +
		"and tm.regionCode=? and tm.tournamentName=? " +
		"order by tm.startTimeUtc desc limit 6"
	result:=mysql.FindAll(sql,
		home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],
		//home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],
	)

	//获取对手的评分
	teamId,_:=strconv.ParseInt(home["teamId"].(string),10,64)
	ff1:=make([]interface{},0)
	ff2:=make([]interface{},0)
	for _,v:=range result {
		isHome:=false
		betId,_:=strconv.ParseInt(v["homeId"].(string),10,64)

		if betId==teamId{
			isHome=true
			betId,_=strconv.ParseInt(v["awayId"].(string),10,64)
		}
		/*d:=map[string]interface{}{
			"teamId":betId,
			"startTimeUtc":v["startTimeUtc"],
			"season":v["season"],
			"regionCode":v["regionCode"],
			"tournamentName":v["tournamentName"],
		}
		betRating:=teamRating(d,0)
		ratingDiff:=rating-betRating

		ff1=append(ff1,ratingDiff)*/
		homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
		awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)
		if (isHome&&homeScore-awayScore>0)||(!isHome&&homeScore-awayScore<0){
			ff2=append(ff2,0)
		}else if(homeScore-awayScore==0){
			ff2=append(ff2,1)
		}else{
			ff2=append(ff2,2)
		}

	}
	/*size:=6-len(ff1)
	for i:=0;i<size ;i++  {
		ff1=append(ff1,-9999)
	}*/
	//胜平负

	size:=6-len(ff2)
	for i:=0;i<size ;i++  {
		ff2=append(ff2,-9999)
	}

	return ff1,ff2
}
//上六场对战
func last6Fight(team1 interface{},team2 interface{}) ([]interface{},[]interface{}) {
	sql:="select t1.*,t2.rating as awayRating from team_match t1,team_match t2 " +
		"where t1.matchId=t2.matchId and t1.teamId=? and t1.startTimeUtc<? and t1.regionCode=? and t1.tournamentName=? " +
		"and t2.teamId=? " +
		"order by t1.startTimeUtc desc limit 2"
	home:=team1.(map[string]interface{})
	away:=team2.(map[string]interface{})
	result:=mysql.FindAll(sql,home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],away["teamId"])

	ff1:=make([]interface{},0)
	for _,v:=range result {
		homeRating,_:=strconv.ParseFloat(v["rating"].(string),64)
		awayRating,_:=strconv.ParseFloat(v["awayRating"].(string),64)
		ff1=append(ff1,homeRating-awayRating)
	}
	size:=2-len(ff1)
	for i:=0;i<size ;i++  {
		ff1=append(ff1,-9999)
	}
	//胜平负
	ff2:=make([]interface{},0)
	for _,v:=range result {
		isHome,_:=strconv.ParseFloat(v["isHome"].(string),64)
		homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
		awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)
		if (isHome==1&&homeScore-awayScore>0)||(isHome==0&&homeScore-awayScore<0){
			ff2=append(ff2,0)
		}else if(homeScore-awayScore==0){
			ff2=append(ff2,1)
		}else{
			ff2=append(ff2,2)
		}

	}
	size=2-len(ff2)
	for i:=0;i<size ;i++  {
		ff2=append(ff2,-9999)
	}

	return ff1,ff2
}

//球探比赛id
func qtMatch(team1 interface{},team2 interface{}) (bool,[]interface{}) {
	sql1:="select * from qt_team where wid=?"
	sql2:="select * from qt_match where home=? and away=? and startAt>=? and startAt<=?"
	home:=team1.(map[string]interface{})
	away:=team2.(map[string]interface{})
	startTimeUtc:=home["startTimeUtc"].(string)

	time1,_:=time.Parse("2006-01-02 15:04:05",startTimeUtc)
	//两个网站的开始时间可能相差10几分钟
	t:=time1.Add(time.Hour*8)
	t2:=t.Add(time.Minute*30)
	t1:=t.Add(time.Minute*-30)
	date:=t.Format("2006-01-02 15:04:05")
	startAt:=t1.Format("2006-01-02 15:04:05")
	endAt:=t2.Format("2006-01-02 15:04:05")

	qtHomeTeam:=mysql.FindFirst(sql1,home["teamId"])
	homeName:=qtHomeTeam["name"].(string)
	qtAwayTeam:=mysql.FindFirst(sql1,away["teamId"])
	awayName:=qtAwayTeam["name"].(string)

	match:=mysql.FindFirst(sql2,homeName,awayName,startAt,endAt)
	if match==nil{
		return false,nil
	}
	//fmt.Println(homeName,awayName,startAt,endAt,match)
	return true,[]interface{}{date,match["id"].(int64),match["league"].(string),homeName,awayName}
}


func lastTry(fn func([]interface{},int))  {
	sql:="select * from `match` where " +
		"(regionCode='tr' or regionCode='es' " +
		"or regionCode='it' or regionCode='gb-eng' " +
		"or regionCode='de' " +
		"or regionCode='nl' or regionCode='ru' " +
		"or regionCode='br' or regionCode='no' " +
		"or regionCode='fr' or regionCode='pt' " +
		"or regionCode='se') " +
		//"and tournamentName!='Championship' and tournamentName!='Bundesliga II' " +
		//"regionCode='tr' "+
		"and detailed=1 "+
		//"regionName='England' and tournamentName='Championship' and detailed=1 " +
		//"and startTimeUtc<'2017-12-31 16:00:00' " +
		"and startTimeUtc>'2016-01-01 00:00:00' " +
		"order by startTimeUtc"
	sql2:="select * from team_match where matchId=? order by isHome"
	matchs:=mysql.FindAll(sql)
	i:=0
	for _,v:=range matchs {

		data:=mysql.FindAll(sql2,v["matchId"])
		if len(data)==0{
			continue
		}
		home:=data[1]
		away:=data[0]

		b,mm:=qtMatch(home,away)
		if !b{
			continue
		}
		qiutan.Format(mm[0].(string),
			mm[1].(int64),
			mm[2].(string),
			mm[3].(string),
			mm[4].(string),
			func(data1 []float64,data2 []float64,data3[]float64,eu []float64,ou []float64) {

			//主队评分
			homeRating:=teamRating(home,0)
			//客队评分
			awayRating:=teamRating(away,0)
			//主队主场评分
			homeHomeRating:=teamRating(home,1)
			//客队客场评分
			awayAwayRating:=teamRating(away,2)


			//上一场比赛情况
			//hlm:=teamLastMatch(home,homeLastSeasonRating)
			//alm:=teamLastMatch(away,awayLastSeasonRating)
			//球员评分
			homePlayerStat:=playerStat(home,true)
			awayPlayerStat:=playerStat(away,false)
			homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
			awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)


			//_,h2:=last6Match(home,0)

			//_,a2:=last6Match(away,0)

			//_,f2:=last6Fight(home,away)

			w:=0

			if homeScore-awayScore==0{
				w=1
			}
			if homeScore-awayScore<0{
				w=2
			}
			if math.Abs(math.Abs(data1[1])-math.Abs(data1[4]))!=0.25{
				//return
			}

			slice := []interface{}{
				//v["matchId"],
				w,
				//homeScore,awayScore,
				//v["regionCode"],
				ou[0],
				data1[1],
				homeRating,awayRating,
				homeHomeRating,awayAwayRating,


			}

			ff1:=[][]interface{}{}

			ff2:=[][]float64{homePlayerStat,awayPlayerStat,data2,data3,eu}
			for _, v := range ff1 {
				for _, vv := range v {
					slice = append(slice, vv)
				}

			}
			for _, v := range ff2 {
				for _, vv := range v {
					slice = append(slice, vv)
				}

			}
			i++
			fmt.Println(i,slice)
			fn(slice,i)
		})

	}
}
func main()  {


	e:=[]string{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}
	xlsx := excelize.NewFile()

	categories := []string{

	}
	for k,v:=range categories{
		len:=int(k/26)
		s:=""
		if len>0{
			s+=e[len-1]+e[k%26]
		}else{
			s+=e[k%26]
		}

		xlsx.SetCellValue("Sheet1",s+"1", v)
	}
	lastTry(func(s []interface{}, i int) {
		for k,v:=range s{
			len:=int(k/26)
			s:=""
			if len>0{
				s+=e[len-1]+e[k%26]
			}else{
				s+=e[k%26]
			}
			xlsx.SetCellValue("Sheet1",s+strconv.Itoa(i+1), v)
		}
	})
	err := xlsx.SaveAs("./Workbook.xlsx")
	if err != nil {
		fmt.Println(err)
	}
}
